import duckdb, os, pandas as pd
from utils.path_utils import get_project_root
from sqlalchemy import create_engine
from flask import Blueprint, jsonify
from service.sjcl_u8 import sjcl_pz, sjcl_wldw, sjcl_bmzd, sjcl_xmzd, sjcl_kmzd, sjcl_kmyeb, sjcl_xmyeb
from config import ini_op
import time
from database.duckdb.duckdb_base import get_connection

# app = Flask(__name__)
# 创建 Blueprint
u8_update_controller = Blueprint('u8update', __name__)
mysql_engine = create_engine("mysql+pymysql://root:111307@localhost:3306/u8")


@u8_update_controller.route('/update', methods=['POST'])
def u8_update():
    conn = get_connection()
    dwbh, dwid, zbid, zbbh, kjnd, kmtx_id, zb = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'ledger', 'zbbh', 'kjnd', 'kmtx_id', 'zbbh')

    #凭证目录
    query = "SELECT * FROM gl_pzml"
    df = pd.read_sql(query, mysql_engine)
    conn.execute("CREATE OR REPLACE TABLE gl_pzml AS SELECT * FROM df")

    #凭证内容
    query = "SELECT * FROM gl_pznr"
    df = pd.read_sql(query, mysql_engine)
    conn.execute("CREATE OR REPLACE TABLE gl_pznr AS SELECT * FROM df")
    sjcl_pz.run(dwbh, dwid, zbid, zbbh, '20250101', '20251231', str(time.strftime("%Y%m%d%H%M%S")))

    #项目字典
    query = "SELECT * FROM gl_xmzl"
    df = pd.read_sql(query, mysql_engine)
    conn.execute("CREATE OR REPLACE TABLE gl_xmzl AS SELECT * FROM df")
    sjcl_xmzd.run(dwbh, dwid, kjnd, str(time.strftime("%Y%m%d%H%M%S")))

    #项目余额表
    query = "SELECT * FROM GL_Fzye"
    df = pd.read_sql(query, mysql_engine)
    conn.execute("CREATE OR REPLACE TABLE GL_Fzye AS SELECT * FROM df")
    sjcl_xmyeb.run(dwbh, zbid, zb, kjnd, str(time.strftime("%Y%m%d%H%M%S")))

    #科目字典
    query = "SELECT * FROM GL_Kmxx"
    df = pd.read_sql(query, mysql_engine)
    conn.execute("CREATE OR REPLACE TABLE GL_Kmxx AS SELECT * FROM df")
    sjcl_kmzd.run(dwbh, dwid, kmtx_id, zb, kjnd, str(time.strftime("%Y%m%d%H%M%S")))

    #科目余额
    query = "SELECT * FROM GL_Kmye"
    df = pd.read_sql(query, mysql_engine)
    conn.execute("CREATE OR REPLACE TABLE GL_Kmye AS SELECT * FROM df")
    sjcl_kmyeb.run(zbid, kjnd, str(time.strftime("%Y%m%d%H%M%S")))

    #部门字典
    query = "SELECT * FROM pubbmxx"
    df = pd.read_sql(query, mysql_engine)
    conn.execute("CREATE OR REPLACE TABLE pubbmxx AS SELECT * FROM df")
    sjcl_bmzd.run()

    #往来单位字典
    query = "SELECT * FROM pubkszl"
    df = pd.read_sql(query, mysql_engine)
    conn.execute("CREATE OR REPLACE TABLE pubkszl AS SELECT * FROM df")
    sjcl_wldw.run(str(time.strftime("%Y%m%d%H%M%S")))

    conn.close()
    return jsonify({'message': 'success'})


